Alter PROCEDURE [dbo].[SPMessagesFlagsGetAll]
(
	@StartIndex int = 0
	,@Length int = 10
)
AS
/*
	Lists all flagged messages (not topics)
*/

SELECT *
FROM
	(SELECT
		ROW_NUMBER() OVER (ORDER BY COUNT(FlagId) DESC, F.TopicId) AS RowNumber
		,F.TopicId
		,F.MessageId
		,COUNT(FlagId) AS TotalFlags
		,T.TopicTitle
		,T.TopicShortName
		,Forums.ForumId
		,Forums.ForumShortName
		,Forums.ForumName
		,M.MessageBody
		,M.UserName
		,M.UserId
	FROM
		Flags F
		INNER JOIN Topics T ON T.TopicId = F.TopicId
		INNER JOIN Forums ON Forums.ForumId = T.ForumId
		INNER JOIN MessagesComplete M ON M.TopicId = T.TopicId AND M.MessageId = F.MessageId
	WHERE
		T.Active = 1
		AND	
		M.Active = 1
	GROUP BY	
		F.TopicId
		,F.MessageId
		,T.TopicTitle
		,T.TopicShortName
		,Forums.ForumId
		,Forums.ForumShortName
		,Forums.ForumName
		,M.MessageBody
		,M.UserName
		,M.UserId
	) T
WHERE
	RowNumber BETWEEN @StartIndex+1 AND @StartIndex + @Length
	
GO


IF EXISTS(SELECT * FROM [dbo].[sysobjects]
	WHERE ID = object_id(N'[dbo].[SPMessagesFlagGetCount]') 
	AND xtype in ('P'))
	DROP PROCEDURE SPMessagesFlagGetCount;
GO

Create procedure SPMessagesFlagGetCount
As
Begin

	Select Count(*) as [Count]
	FROM
		Flags F
		INNER JOIN Topics T ON T.TopicId = F.TopicId
		INNER JOIN Forums ON Forums.ForumId = T.ForumId
		INNER JOIN MessagesComplete M ON M.TopicId = T.TopicId AND M.MessageId = F.MessageId
	WHERE
		T.Active = 1
		AND	
		M.Active = 1
End
go
